import pandas as pd
import numpy as np
import time
import openpyxl

class data_config:
    def __init__(self) -> None:
        self.source_path = ''
        self.source_head = []
        self.source_head_count = 0

        self.model_path = ''
        self.model_head = []
        self.model_head_count = 0

        self.count_per_file = 1
        self.to_path = ''

        # 一样共有model_head_count项，每项的取值范围是[0, model_head_count-1]
        self.column_index = []
        self.column_index_text = []

        # 固定内容
        self.fixed_content_text = []

        # 去除空格
        self.despace_enable = []

        # 规则
        self.rules_index = []
        self.rules_text = []

        # 自定义截取字符串始末
        self.rules_substr_begin = []
        self.rules_substr_end = []

        # 默认输出格式是xls
        self.export_format = 'xls'

    def set_source(self, file_path):
        '''
        设置源文件，返回头列表
        '''
        if file_path.lower().endswith(('.csv', '.xlsx', '.xls')):
            df = pd.read_excel(file_path) if file_path.lower().endswith(('.xlsx', '.xls')) else pd.read_csv(file_path)
            self.source_path = file_path
            self.source_head = df.columns.tolist()
            self.source_head_count = len(self.source_head) 
        
        return self.source_head

    def set_model(self, file_path):
        '''
        设置模板文件，返回头列表
        '''
        if file_path.lower().endswith(('.csv', '.xlsx', '.xls')):
            df = pd.read_excel(file_path) if file_path.lower().endswith(('.xlsx', '.xls')) else pd.read_csv(file_path)
            self.model_path = file_path
            self.model_head = df.columns.tolist()
            self.model_head_count = len(self.model_head) 

            for i in range(0, self.model_head_count):
                self.column_index.append(-1)
                self.column_index_text.append('空')

                self.rules_index.append(-1)
                self.rules_text.append('空')

                self.fixed_content_text.append('无')
                self.despace_enable.append(True)

                self.rules_substr_begin.append(-1)
                self.rules_substr_end.append(-1)
        
        return self.model_head

    def set_column(self, column_index, item_index, item_text):
        self.column_index_text[column_index] = item_text
        self.column_index[column_index] = item_index

    def set_rules(self, column_index, item_index, item_text):
        self.rules_text[column_index] = item_text
        self.rules_index[column_index] = item_index

    def set_despace(self, index, ibool):
        self.despace_enable[index] = ibool

    def set_rules_substr(self, index, begin, end):
        if begin > end:
            begin = begin + end
            end = begin - end
            begin = begin - end
        self.rules_substr_begin[index] = begin
        self.rules_substr_end[index] = end

    def set_fixed_content_text(self, index, text):
        self.fixed_content_text[index] = text

    def set_export_format(self, format):
        if format == 'csv' or format == 'xls' or format == 'xlsx':
            self.export_format = format
        else:
            t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            e = f'设置的输出文件格式有误：{format}'
            with open('error.txt','a') as f:
                f.write(f'=====设置输出文件格式错误=====\n{t}\n{e}\n===============\n')
                return False

    def show(self):
        return f"模板表头是:\n{self.model_head}\n对应数据是:\n{self.column_index_text}\n固定值:\n{self.fixed_content_text}\n是否去除空格:\n{self.despace_enable}\n规则:\n{self.rules_text}\n输出格式:\n{self.export_format}"

    def check(self):
        for i in range(self.model_head_count):
            # 检查表头配置
            if self.column_index_text[i] == '空' or self.column_index_text[i] == '（请选择）':
                return f"第{i+1}行未配置表头"
            # 检查固定值配置
            if self.fixed_content_text[i] == '无' or self.fixed_content_text[i] == '（输入固定值）':
                if self.column_index_text[i] == '[固定值]':
                    return f"第{i+1}行需要输入固定值但是未输入"
            # 检查规则选项
            if self.rules_text[i] == '空' or self.rules_text[i] == '(请选择提取规则)':
                if self.column_index_text[i] != '[固定值]' and self.column_index_text[i] != '[忽略]':
                    return f"第{i+1}行需要配置规则但是未配置"
            if self.rules_text[i] == '截取指定位数' and (self.rules_substr_begin[i] <= 0 or self.rules_substr_end[i] <= 0):
                return f"第{i+1}行的规则是截取指定位数，但是配置始末位置错误"

        return 'checked'
        
    def do_change_column(self):

        index = 1
        begin = 0
        end = begin + self.count_per_file

        try:
            model_file = self.model_path   
            print('model_file ' + model_file)
            source_file = self.source_path  
            print('source_file ' + source_file) 
            if model_file.lower().endswith(('.csv', '.xlsx', '.xls')):
                model_data = pd.read_excel(model_file) if model_file.lower().endswith(('.xlsx', '.xls')) else pd.read_csv(model_file)
            if source_file.lower().endswith(('.csv', '.xlsx', '.xls')):
                source_data = pd.read_excel(source_file) if source_file.lower().endswith(('.xlsx', '.xls')) else pd.read_csv(source_file)
        except Exception as e:
            print(e)
            t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            with open('error.txt','a') as f:
                f.write(f'=====打开文件错误=====\n{t}\n{e}\n===============\n')
                return False

        # check cloumn
        real_cloumn = []
        insert_null = 0
        insert_fix = 0
        try:
            for i in range(0, len(self.column_index_text)):
                if self.column_index_text[i] == '[忽略]':
                    change = f'null{insert_null}'
                    source_data[change] = ''
                    real_cloumn.append(change)
                    insert_null+=1
                elif self.column_index_text[i] == '[固定值]':
                    change = f'fix{insert_fix}'
                    source_data[change] = self.fixed_content_text[i]
                    real_cloumn.append(change)
                    insert_fix+=1
                else:
                    real_cloumn.append(self.column_index_text[i])
        except Exception as e:
            print(e)
            t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            with open('error.txt','a') as f:
                f.write(f'=====处理配置错误=====\n{t}\n{e}\n===============\n')
                return False

        new_data = source_data[real_cloumn]
        new_data.columns = self.model_head

        # 处理空格
        for i in range(0,self.model_head_count):
            if self.despace_enable[i] == True:
                try:
                    # new_data.iloc[:,i] = new_data.iloc[:,i].str.strip()
                    new_data.loc[:,self.model_head[i]] = astype_str_notna(new_data.loc[:,self.model_head[i]])
                    # new_data[self.model_head[i]] = new_data[self.model_head[i]].str.strip()
                    # new_data.iloc[:,i] = new_data.iloc[:,i].str.strip()
                    new_data.loc[:,self.model_head[i]] = new_data.loc[:,self.model_head[i]].str.strip()
                    print(f"{i} {self.model_head[i]}done")
                except Exception as e:
                    print(f'处理空格时发生错误：{i} : {e}')
                    t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                    with open('error.txt','a') as f:
                        f.write(f'=====处理空格时发生错误====={i}\n{t}\n{e}\n===============\n')
                    return False

        # 处理规则
        try:
            for i in range(0, len(self.column_index_text)):
                if self.rules_text[i] == '卡号截取地区号':
                    new_data.loc[:,self.model_head[i]] = new_data.loc[:,self.model_head[i]].str.slice(6,10) # 第7到10位是地区号
                elif self.rules_text[i] == '账号截取地区号':
                    new_data.loc[:,self.model_head[i]] = new_data.loc[:,self.model_head[i]].str.slice(0,4) # 前4位是地区号
                elif self.rules_text[i] == '截取指定位数':
                    print(f"第{i}行截取字符串： 从{self.rules_substr_begin[i]-1}到{self.rules_substr_end[i]}")
                    new_data.loc[:,self.model_head[i]] = new_data.loc[:,self.model_head[i]].str.slice(self.rules_substr_begin[i]-1,self.rules_substr_end[i]) # 前4位是地区号

        except Exception as e:
            print(e)
            t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            with open('error.txt','a') as f:
                f.write(f'=====处理规则发生错误=====\n{t}\n{e}\n===============\n')
                return False
      
        l = len(source_data)
        all = l//self.count_per_file
        if l%self.count_per_file != 0:
            all += 1

        try:
            while True:
                if index > all:
                    break
                save_data = model_data._append(new_data[begin:end])
                if self.export_format == 'xls':
                    save_data.to_excel(self.to_path + f'-{index}of{all}.xls', index=False, engine='xlsxwriter')    #
                elif self.export_format == 'xlsx':
                    save_data.to_excel(self.to_path + f'-{index}of{all}.xlsx', index=False, engine='xlsxwriter')    #
                elif self.export_format == 'csv':
                    save_data.to_csv(self.to_path + f'-{index}of{all}.csv', index=False)    #
                if end > l:
                    break
                else:
                    index += 1
                    begin = end
                    end = begin + self.count_per_file
        except Exception as e:
            print(e)
            t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            with open('error.txt','a') as f:
                f.write(f'=====分割文件出错=====\n{t}\n{e}\n===============\n')
                return False

        return True

def astype_str_notna(df):
    '''
    传入参数：数据框里面一列  Series
    
    return：转换后的一列  Series
    '''
    t=[]
    for i in df:
        if type(i)== float:
            if not np.isnan(i):
                i=str(int(i))
        if type(i)== int:
            i=str(i)
            
        t.append(i)
     
    
    return pd.Series(t)


if __name__ == '__main__':
    dc = data_config()
    print(dc.set_source('C:/Users/Allan/Desktop/workspace/小工具/gadgets/findData/a表-脱敏.csv'))
    print(dc.set_model('C:/Users/Allan/Desktop/workspace/小工具/gadgets/findData/b表-脱敏.xls'))
    print('done')

